package com.atguigu.yuntai.statistics.mapper;

import com.atguigu.yuntai.statistics.entity.AdsUserRetention;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 用户留存率Mapper
 */
@DS("mysql-gmall-report")
public interface UserRetentionMapper extends BaseMapper<AdsUserRetention> {

    /**
     * 查询规定时间近7日用户留存
     *
     * @param dt
     * @return
     */
    @Select("SELECT      create_date , MAX( new_user_count ) new_user_count , " +
            "   SUM( IF(retention_day=1, retention_rate,0))  retention_rate_d1,   " +
            "    SUM( IF(retention_day=2, retention_rate,0))  retention_rate_d2,  " +
            "    SUM( IF(retention_day=3, retention_rate,0))  retention_rate_d3,  " +
            "    SUM( IF(retention_day=4, retention_rate,0))  retention_rate_d4,  " +
            "     SUM( IF(retention_day=5, retention_rate,0))  retention_rate_d5,    " +
            "    SUM( IF(retention_day=6, retention_rate,0))  retention_rate_d6,    " +
            "    SUM( IF(retention_day=7, retention_rate,0))  retention_rate_d7   " +
            "   FROM  ads_user_retention  where   dt BETWEEN date_sub(#{dt}, interval 7 day) and #{dt} " +
            "  group by create_date")
    List<Map> getUserRetention(@Param("dt") String dt);

    /**
     * 查询昨日用户留存
     *
     * @return
     */
    @Select("SELECT dt,create_date,retention_day,retention_count,new_user_count,retention_rate FROM ads_user_retention WHERE TO_DAYS(NOW())-TO_DAYS(dt)=1")
    public List<AdsUserRetention> getAdsUserRetentionYesterday();

    /**
     * 查询进入用户留存
     *
     * @return
     */
    @Select("SELECT dt,create_date,retention_day,retention_count,new_user_count,retention_rate FROM ads_user_retention WHERE TO_DAYS(NOW())=TO_DAYS(dt)")
    public List<AdsUserRetention> getAdsUserRetentionToday();

}
